package com.google.code.ptrends.Application.DAL;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import com.google.code.ptrends.Application.DAL.entities.ItemEntity;
import com.google.code.ptrends.Application.DAL.entities.PriceEntity;

@Stateless
@LocalBean
public class TrendsServiceBean implements Serializable {

	private static final long serialVersionUID = 1L;

	@PersistenceContext(name = "Application.DAL")
	private transient EntityManager entityManager;

	public TrendsServiceBean() {
	}

	private String getStrWithLikeByParameter(String parameterStr) {
		String str = parameterStr.trim();
		return "UPPER(p.value) LIKE '%" + str.toUpperCase(Locale.US) + "%'";
	}

	private String getItemParametersQueryString(String params) {
		StringBuilder sb = new StringBuilder();
		String[] arr = params.split(",");
		for (int i = 0; i < arr.length; i++) {
			sb.append(getStrWithLikeByParameter(arr[i]));
			if (i != arr.length - 1)
				sb.append(" OR ");
		}
		return sb.toString();
	}

	public List<ItemEntity> getAllItemsByParameters(Parameters params) {
		TypedQuery<ItemEntity> query = entityManager.createQuery(
				"SELECT DISTINCT c FROM ItemEntity c "
						+ "INNER JOIN c.parameters p " + "WHERE "
						+ "UPPER(c.manufacturer.name) LIKE :manufacturer AND "
						+ "UPPER(c.category.name) LIKE :category AND "
						+ "UPPER(c.classes.name) LIKE :classes AND "
						+ "UPPER(c.model) LIKE :model AND ("
						+ getItemParametersQueryString(params.getParams())
						+ ")", ItemEntity.class);
		query.setParameter("manufacturer", "%"
				+ params.getManufacturer().toUpperCase(Locale.US) + "%");
		query.setParameter("category",
				"%" + params.getCategory().toUpperCase(Locale.US) + "%");
		query.setParameter("classes",
				"%" + params.getItemClass().toUpperCase(Locale.US) + "%");
		query.setParameter("model",
				"%" + params.getModel().toUpperCase(Locale.US) + "%");
		return query.getResultList();
	}

	public List<String> getAllSuppliers() {
		TypedQuery<String> query = entityManager.createQuery(
				"SELECT c.name FROM SupplierEntity c", String.class);
		return query.getResultList();
	}

	public List<PriceEntity> getItemPrices(int itemId, String supplierName,
			Date startDate, Date endDate) {
		SimpleDateFormat dateformatYYYYMMDD = new SimpleDateFormat("yyyy-MM-dd");
		StringBuilder startSB = new StringBuilder(
				dateformatYYYYMMDD.format(startDate));
		startSB.append(" 00:00:00.0");
		StringBuilder endSB = new StringBuilder(
				dateformatYYYYMMDD.format(endDate));
		endSB.append(" 23:59:59.0");
		TypedQuery<PriceEntity> query = entityManager.createQuery(
				"SELECT p FROM PriceEntity p "
						+ "WHERE p.item.id = :itemId and "
						+ "UPPER(p.supplier.name) = :supplierName and "
						+ "(p.priceDate >= '" + startSB.toString()
						+ "' and p.priceDate <= '" + endSB.toString() + "')",
				PriceEntity.class);
		query.setParameter("itemId", itemId);
		query.setParameter("supplierName", supplierName.toUpperCase(Locale.US));
		// query.setParameter("startDate", startSB.toString());
		// query.setParameter("endDate", endSB.toString());
		return query.getResultList();
	}

}
